package com.elitel.frame.base.service.impl;

import com.elitel.frame.base.component.RedisCacheComponent;
import com.elitel.frame.base.component.RedisCacheDataBase;
import com.elitel.frame.base.component.exportprojectdata.DataBaseConnInfo;
import com.elitel.frame.base.service.BaseQueryfieldService;
import com.elitel.frame.base.service.BaseQuerysqlService;
import com.elitel.frame.base.service.CfgExtmasterService;
import com.elitel.frame.main.dao.*;
import com.elitel.frame.main.dao.ext.ExtBaseQuerysqlMapper;
import com.elitel.frame.main.entity.*;
import com.elitel.frame.main.entity.ext.BaseQueryfieldExt;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * @author guoyanfei
 * @description 服务查询列sql文表接口实现类
 * @date 2017/11/24
 */
@Service
public class BaseQuerysqlServiceImpl implements BaseQuerysqlService {
    @Autowired
    private BaseQuerysqlMapper baseQuerysqlMapper;
    @Autowired
    private ExtBaseQuerysqlMapper extBaseQuerysqlMapper;
    @Autowired
    private BaseQueryfieldService baseQueryfieldService;
    @Autowired
    private RedisCacheDataBase redisCacheDataBase;
    @Autowired
    private CfgExtendMapper cfgExtendMapper;
    @Autowired
    private CfgExtmasterService cfgExtmasterService;
    @Autowired
    private CfgDbMapper cfgDbMapper;
    @Autowired
    private CfgExtmasterMapper cfgExtmasterMapper;
    @Autowired
    private CfgExttreeMapper cfgExttreeMapper;
    @Autowired
    private DataBaseConnInfo dataBaseConnInfo;
    @Autowired
    private RedisCacheComponent redisCacheComponent;
    @Autowired
    private BaseFieldaddonsMapper baseFieldaddonsMapper;

    @Override
    public void editBaseQuerysql(String serviceId, String serviceType, String grade) {
//		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String databaseType = dataBaseConnInfo.dataBaseType;
        String dbkey = "";
        StringBuilder sqltxt = new StringBuilder();
        CfgTable cfgTable = null;
        switch (serviceType) {
            case "1":
                //单表服务
                CfgExtend cfgExtend = cfgExtendMapper.selectByPrimaryKey(serviceId);
                cfgTable = redisCacheComponent.cfgTableRedisCache(cfgExtend.getDtConfigId(), cfgExtend.getDbKey());
                if (cfgExtend != null) {
                    CfgDb cfgDb = cfgDbMapper.selectByPrimaryKey(cfgExtend.getDbKey());
                    if (cfgDb != null) {
                        dbkey = cfgDb.getDbVersion();
                    }
                }
                break;
            case "2":
                //主从表服务
                CfgExtmaster cfgExtmaster = cfgExtmasterMapper.selectByPrimaryKey(serviceId);
                if (cfgExtmaster != null) {
                    CfgDb cfgDb = cfgDbMapper.selectByPrimaryKey(cfgExtmaster.getDbKey());
                    if (cfgDb != null) {
                        dbkey = cfgDb.getDbVersion();
                    }
                }
                break;
            case "3":
                //资源树服务
                CfgExttree cfgExttree = cfgExttreeMapper.selectByPrimaryKey(serviceId);
                if (cfgExttree != null) {
                    CfgDb cfgDb = cfgDbMapper.selectByPrimaryKey(cfgExttree.getDtDbKey());
                    if (cfgDb != null) {
                        dbkey = cfgDb.getDbVersion();
                    }
                }
                break;
            default:
                break;
        }

        List<BaseQueryfieldExt> baseQueryfieldExts = baseQueryfieldService.getQueryFieldListByServiceId(serviceId, serviceType, grade, null);
        if (baseQueryfieldExts != null && baseQueryfieldExts.size() > 0) {
            for (BaseQueryfieldExt baseQueryfieldExt : baseQueryfieldExts) {
                String tableName = "";
                if (cfgTable != null && cfgTable.getDtTabletype() == 2) {
                    BaseFieldaddons baseFieldaddons = baseFieldaddonsMapper.selectByPrimaryKey(baseQueryfieldExt.getFieldaddonsguid());
                    tableName = baseFieldaddons.getAltername() + ".";
                }

                if (baseQueryfieldExt.getParametertype().equals(1)) {
                    //动态参数
                    if (baseQueryfieldExt.getParametercondition() != null && !baseQueryfieldExt.getParametercondition().equals("")) {
                        sqltxt.append("<#if ").append(baseQueryfieldExt.getParametercode()).append("?? && ").append(baseQueryfieldExt.getParametercode()).append("!=''>");
                        if (baseQueryfieldExt.getParametercondition().equals("like")) {
                            sqltxt.append(" and ").append(tableName).append(parameterFormatter(dbkey, baseQueryfieldExt.getFieldname(), baseQueryfieldExt.getParamformat(), baseQueryfieldExt.getFieldtype())).append(" ").append(baseQueryfieldExt.getParametercondition()).append(" '%${").append(baseQueryfieldExt.getParametercode()).append("}%'");
                        } else {
                            sqltxt.append(" and ").append(tableName).append(parameterFormatter(dbkey, baseQueryfieldExt.getFieldname(), baseQueryfieldExt.getParamformat(), baseQueryfieldExt.getFieldtype())).append(" ").append(baseQueryfieldExt.getParametercondition()).append(" '${").append(baseQueryfieldExt.getParametercode()).append("}'");
                        }
                        sqltxt.append("</#if>");
                    }
                } else if (baseQueryfieldExt.getParametertype().equals(2)) {
                    //固定参数
                    if (baseQueryfieldExt.getParametercondition() != null && !baseQueryfieldExt.getParametercondition().equals("")) {
                        if (baseQueryfieldExt.getParametercondition().equals("like")) {
                            sqltxt.append(" and ").append(tableName).append(baseQueryfieldExt.getFieldname()).append(" ").append(baseQueryfieldExt.getParametercondition()).append(" '%").append(baseQueryfieldExt.getParametervalue()).append("%'");
                        } else {
                            sqltxt.append(" and ").append(tableName).append(baseQueryfieldExt.getFieldname()).append(" ").append(baseQueryfieldExt.getParametercondition()).append(" '").append(baseQueryfieldExt.getParametervalue()).append("'");
                        }
                    }
                }
            }

        }

        BaseQuerysql baseQuerysql = null;
        /*switch (databaseType) {
            case "oracle":
                baseQuerysql = extBaseQuerysqlMapper.getBasequerysqlbyService(serviceId, serviceType, grade);
                break;
            case "mysql":
            case "sqlite":
            case "h2":
            case DataSourceType.POSTGRES:
                baseQuerysql = extBaseQuerysqlMapper.getBasequerysqlbyServiceMysql(serviceId, serviceType, grade);
                break;
        }*/
        baseQuerysql = extBaseQuerysqlMapper.getBasequerysqlbyServiceMysql(serviceId, serviceType, grade);

        if (baseQuerysql != null) {
            //修改
            baseQuerysql.setSqlcontent(sqltxt.toString());
            baseQuerysqlMapper.updateByPrimaryKey(baseQuerysql);
        } else {
            //新增
            baseQuerysql = new BaseQuerysql();
            baseQuerysql.setGuid(UUID.randomUUID().toString());
            baseQuerysql.setServiceid(serviceId);
            baseQuerysql.setServicetype(serviceType);
            baseQuerysql.setGrade(grade);
            baseQuerysql.setCreatedate(new Date());
            baseQuerysql.setSqlcontent(sqltxt.toString());
            baseQuerysqlMapper.insert(baseQuerysql);
        }

        //刷新服务
        redisCacheDataBase.refreshBaseQuerysql(serviceId, serviceType, grade);

    }

    /**
     * @param dbtype(数据库类型:ORACLE,MYSQL,SQLSERVER)
     * @param fieldName(字段名称)
     * @return
     * @description 查询条件参数格式化
     * @author guoyanfei
     * @date 2017/12/4
     * ModifyBy guoyanfei 2018/12/19
     * 新增条件只有字段类型是date时，才需要时间格式转换
     */
    private String parameterFormatter(String dbtype, String fieldName, String paramFormat, String fieldtype) {
        String paramTxt = "";
        if (fieldtype != null && !fieldtype.equals("date")) {
            paramTxt = fieldName;
            return paramTxt;
        }
        switch (dbtype) {
            case "ORACLE":
                switch (paramFormat) {
                    case "int":
                        paramTxt = fieldName;
                        break;
                    case "string":
                        paramTxt = fieldName;
                        break;
                    case "year":
                        paramTxt = " to_CHAR(" + fieldName + ",'yyyy')";
                        break;
                    case "month":
                        paramTxt = " to_CHAR(" + fieldName + ",'MM')";
                        break;
                    case "day":
                        paramTxt = " to_CHAR(" + fieldName + ",'dd')";
                        break;
                    case "ymonth":
                        paramTxt = " to_CHAR(" + fieldName + ",'yyyy-MM')";
                        break;
                    case "date":
                        paramTxt = " to_CHAR(" + fieldName + ",'yyyy-MM-dd')";
                        break;
                    case "datehour":
                        paramTxt = " to_CHAR(" + fieldName + ",'yyyy-MM-dd hh24')";
                        break;
                    case "datehmin":
                        paramTxt = " to_CHAR(" + fieldName + ",'yyyy-MM-dd hh24:mi')";
                        break;
                    case "datetime":
                        paramTxt = " to_CHAR(" + fieldName + ",'yyyy-MM-dd hh24:mi:ss')";
                        break;
                    default:
                        break;
                }
                break;
            case "MYSQL":
                switch (paramFormat) {
                    case "int":
                        paramTxt = fieldName;
                        break;
                    case "string":
                        paramTxt = fieldName;
                        break;
                    case "year":
                        paramTxt = " year(" + fieldName + ")";
                        break;
                    case "month":
                        paramTxt = " month(" + fieldName + ")";
                        break;
                    case "day":
                        paramTxt = " DAY(" + fieldName + ")";
                        break;
                    case "ymonth":
                        paramTxt = " str_to_date('" + fieldName + "','%Y-%m')";
                        break;
                    case "date":
                        paramTxt = " str_to_date('" + fieldName + "','%Y-%m-%d')";
                        break;
                    case "datehour":
                        paramTxt = " str_to_date('" + fieldName + "','%Y-%m-%d %H')";
                        break;
                    case "datehmin":
                        paramTxt = " str_to_date('" + fieldName + "','%Y-%m-%d %H:%i')";
                        break;
                    case "datetime":
                        paramTxt = " str_to_date('" + fieldName + "','%Y-%m-%d %H:%i:%S')";
                        break;
                    default:
                        break;
                }
                break;
            case "SQLSERVER":
                switch (paramFormat) {
                    case "int":
                        paramTxt = fieldName;
                        break;
                    case "string":
                        paramTxt = fieldName;
                        break;
                    case "year":
                        paramTxt = " year(" + fieldName + ")";
                        break;
                    case "month":
                        paramTxt = " month(" + fieldName + ")";
                        break;
                    case "day":
                        paramTxt = " DAY(" + fieldName + ")";
                        break;
                    case "ymonth":
                        paramTxt = fieldName;
                        break;
                    case "date":
                        paramTxt = fieldName;
                        break;
                    case "datehour":
                        paramTxt = fieldName;
                        break;
                    case "datehmin":
                        paramTxt = fieldName;
                        break;
                    case "datetime":
                        paramTxt = fieldName;
                        break;
                    default:
                        break;
                }
                break;
            default:
                paramTxt = fieldName;
                break;
        }
        return paramTxt;
    }

    @Override
    public BaseQuerysql getBaseQuerysql(String serviceId, String serviceType, String grade) {
        String databaseType = dataBaseConnInfo.dataBaseType;
        BaseQuerysql baseQuerysql = new BaseQuerysql();
        /*switch (databaseType) {
            case "oracle":
                baseQuerysql = extBaseQuerysqlMapper.getBasequerysqlbyService(serviceId, serviceType, grade);
                break;
            case "mysql":
            case "sqlite":
            case "h2":
            case DataSourceType.POSTGRES:
                baseQuerysql = extBaseQuerysqlMapper.getBasequerysqlbyServiceMysql(serviceId, serviceType, grade);
                break;
        }*/
        baseQuerysql = extBaseQuerysqlMapper.getBasequerysqlbyServiceMysql(serviceId, serviceType, grade);

        return baseQuerysql;
    }

}
